package job;

import java.sql.Date;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.List;

import javax.persistence.Query;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import play.db.jpa.JPA;
import play.jobs.Job;
import play.jobs.On;
import play.jobs.OnApplicationStart;
import utils.PropertyUtil;
/**
 * 将scheduleprice表发车日期在60天以前的记录 迁移  到schedulepricehis表
 * 每天晚上运行一次
 * @author bus365
 *
 */
@SuppressWarnings("rawtypes")
//@OnApplicationStart(async=true)
@On("0 0 1 * * ?")
public class SchedulepriceMoveJob extends Job {
	
	final private Logger log = LoggerFactory.getLogger(getClass());
	public void doJob() throws Exception {

		try {
			String PARAMETER_SQL = "select value from parameter where code='0004'";
			Query movedaysquery = JPA.em().createNativeQuery(PARAMETER_SQL);
			Integer movedays = Integer.valueOf((String)movedaysquery.getSingleResult());
			Calendar calendar = Calendar.getInstance();
			calendar.set(Calendar.DAY_OF_YEAR, calendar.get(Calendar.DAY_OF_YEAR) - movedays);
			SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
			//获取当前时间的前60天的时间
			String time=sdf.format(calendar.getTime());
			Date daye=new Date( sdf.parse(time).getTime());
			/*
			 * 向schedulepricehis表插入数据
			 * 以天为单位循环插入数据
			 * 删除scheduleprice标的数据
			 * 以发车日期为条件，以天为单位循环删除数据
			 */
			//获取所有需要迁移的天数
			String findDayesSql="select DISTINCT(departdate) from scheduleprice where departdate < ?";
			//向Schedulepricehis插入数据
			String insertSchedulepricehisSql="replace into `schedulepricehis` select * from `scheduleprice` where `departdate`=?";
			//删除 Scheduleprice的数据
			String deleteSchedulepriceSql="delete from `scheduleprice`  where `departdate`=?";
			Query query = JPA.em().createNativeQuery(findDayesSql);
			query.setParameter(1, daye);
			List<Date> resultList = query.getResultList();
			for(int i=0;i<resultList.size();i++){
				if(!JPA.em().getTransaction().isActive()){
					JPA.em().getTransaction().begin();
				}
				query =JPA.em().createNativeQuery(insertSchedulepricehisSql);
				query.setParameter(1, resultList.get(i));
				query.executeUpdate();
				query =JPA.em().createNativeQuery(deleteSchedulepriceSql);
				query.setParameter(1, resultList.get(i));
				query.executeUpdate();
				JPA.em().getTransaction().commit();
				
			}
			/*
			 * 向departpassengerhis表插入数据
			 * 以天为单位循环插入数据
			 * 删除departpassenger标的数据
			 * 以发车日期为条件，以天为单位循环删除数据
			 */
			//获取所有需要迁移的天数
			String finddepartDayesSql="select DISTINCT(departdate) from departpassenger where departdate < ?";
			//向Schedulepricehis插入数据
			String insertDepartpassengerhisSql="replace into `departpassengerhis` select * from `departpassenger` where `departdate`=?";
			//删除 Scheduleprice的数据
			String deleteDepartpassengerSql="delete from `departpassenger`  where `departdate`=?";
			Query departquery = JPA.em().createNativeQuery(finddepartDayesSql);
			departquery.setParameter(1, daye);
			List<Date> departResultList = departquery.getResultList();
			for(int i=0;i<departResultList.size();i++){
				if(!JPA.em().getTransaction().isActive()){
					JPA.em().getTransaction().begin();
				}
				departquery =JPA.em().createNativeQuery(insertDepartpassengerhisSql);
				departquery.setParameter(1, departResultList.get(i));
				departquery.executeUpdate();
				departquery =JPA.em().createNativeQuery(deleteDepartpassengerSql);
				departquery.setParameter(1, departResultList.get(i));
				departquery.executeUpdate();
				JPA.em().getTransaction().commit();
				
			}
		} catch (Exception e) {
			JPA.em().getTransaction().rollback();
			log.error("scheduleprice和departpassenger表数据迁移失败",e);
		}
	}
}
